﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using ReflectionStudio.Core.Events;
using INTEX.DataBase;
using INTEX.DataBase.Tools;
using System.Text;
namespace WPFExcelReport
{

    public class YCAWS02Data : BaseFunction 
    {
        public bool checkVK07 = true;
        SETINTEXDB INTEXDB = new SETINTEXDB();
        INTEXDBC INTEXDBC;
        public YCAWS02Data()
            : base()
        {

        }
        public YCAWS02Data(OracleConnection _myConnection, IntexReptServBLClient _proxy, string userID = "        ")
            : base(_myConnection, _proxy, userID)
        {
            INTEXDBC = new INTEXDBC(OracleDB, proxy);
        }
        public YCAWS02Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {

        }
        public DataSet GetWASTAMM(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ICC_WS02";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string TYPE = dwABPOS["TYPE"].ToString().Trim();
            string PRDGRP = dwABPOS["PRDGRP"].ToString().Trim();
            string COLOR = dwABPOS["COLOR"].ToString().Trim();
            string SCOLOR = dwABPOS["SCOLOR"].ToString().Trim();
            string SUPP = dwABPOS["SUPP"].ToString().Trim();
            string SART = dwABPOS["SART"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " select distinct waart,waprogrp,prdgrp,waartgrp,artgrp,supp,supplier,walohna, "
                    + " agg(color||'-'||colorname||'-'||scolor) "
                    + " over(partition by waart,price) as color,price,wauebm, "
                    + " composition,width,wafgrqm,wafgrlfd,ffddik,ffddis,wafddim,waliefnr,wabez,wafbem  "
                    + " from (select a.waart,a.wabez,a.waliefnr,trim(d.lsliefna) as supp, "
                    + " trim(d.lsname1) as supplier,a.walohna, "
                    + " a.wafbrtl*0.1 as width,a.waffddik*0.01 as ffddik, "
                    + " a.waffddis*0.01 as ffddis,a.wafddim,a.wafgrqm,a.wafgrlfd,a.wafbem, "
                    + " a.waprogrp,(select trim(substr(sddaten, 2, 20)) from sddat where sdsa = '0196' "
                    + " and substr(sdkey, 24, 1) = 'V' and trim(substr(sdkey, 3, 15)) = trim(a.waprogrp)) AS PRDGRP, "
                    + " a.waartgrp,(select trim(substr(sddaten, 2, 20)) from sddat where sdsa = '0178' "
                    + " and substr(sdkey, 24, 1) = 'V' and trim(substr(sdkey, 3, 15)) = trim(a.waartgrp)) AS ARTGRP, "
                    + " a.wauebm,B.COMPOSITION,C.COLOR,C.COLORNAME,C.SCOLOR, "
                    + " (CASE WHEN C.PRICE = 0 THEN trim(A.WABIND) WHEN TRIM(C.PRICE) IS NULL  "
                    + " THEN trim(A.WABIND) ELSE TO_CHAR(C.PRICE) END ) AS PRICE  "
                    + " from wastamm a,(SELECT DISTINCT ART,STRAGG(RATE*0.1||'%'||MAT||MATERIAL) "
                    + " OVER(PARTITION BY ART) AS COMPOSITION FROM ( "
                    + " SELECT TRIM(SUBSTR(STUELISAPD,15,20)) AS ART, "
                    + " SUBSTR(STUELIDATEN,1,4) AS RATE,TRIM(STUELISASB) AS MAT, "
                    + " TRIM(SUBSTR(STUELIDATEN,5,20)) AS MATERIAL FROM STUELI  "
                    + " WHERE TRIM(SUBSTR(STUELIDATEN,55,2)) ='R' ORDER BY ART,RATE DESC)) B, "
                    + " (SELECT C.WAFART,TRIM(C.WAFZUSATZ) AS COLOR,TRIM(WAFBEZ) AS COLORNAME,  "
                    + " TRIM(C.WAFMUDESS) AS SCOLOR,C.WAFZUABAK*0.01 AS PRICE "
                    + " FROM WAFARBEN C WHERE C.WAFSTATUS = 'A' AND C.WAFZUART = 'F') C,liefdat d "
                    + " where A.wafkz = 'J' AND A.WAFSTAKZ = 'A'  "
                    + " AND TRIM(A.WAART) = B.ART(+) AND A.WAART = C.WAFART(+) "
                    + " and a.wamandant = d.lsmandant(+) and a.waliefnr = d.lsliefnr(+) "
                    + " and A.WAART like '" + ARTICLE + "%'  "
                    + " and A.walohna like '" + SART + "%'  "
                    + " AND (TRIM(a.waprogrp) = '" + TYPE + "' OR '" + TYPE + "'  = 'XXXXXX') "
                    + " AND (TRIM(A.waartgrp) = '" + PRDGRP + "' OR '" + PRDGRP + "'  = 'XXXXXX') )  "
                    + " WHERE  COLOR like '" + COLOR + "%'  "
                    + " and (SCOLOR like '" + SCOLOR + "%' or SCOLOR IS NULL) "
                    + " and SUPP like '" + SUPP + "%' "                   
                    + " ORDER BY 1 "
                   ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["PRDGRP"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["PRDGRP"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTGRP"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTGRP"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["WALOHNA"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["WALOHNA"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SUPPLIER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SUPPLIER"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COLOR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COMPOSITION"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COMPOSITION"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["WABEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["WABEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["WAFBEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["WAFBEM"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetData(DataRow dwInput)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();

            try
            {
                DataRow dwTemp;
                SResult = GetWASTAMM(dwInput);
                SReturn.Merge(SResult); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            return SReturn;
        } 
    }
}